﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Data;
namespace CRUD_ADO.Net
{
    public class SQLConntectionDB
    {
        private SqlConnection cnn;
        private SqlDataAdapter myAdapter;
        public SQLConntectionDB()
        {
            
                myAdapter = new SqlDataAdapter();
                string strconnect = WebConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
                cnn = new SqlConnection(strconnect);
            
        }
        public SqlConnection openConnection()
        {
            if (cnn.State == ConnectionState.Closed || cnn.State == ConnectionState.Broken)
            {
                cnn.Open();
            }
            return cnn;
        }

      
        public DataTable selectQuery_ExecuteReader(string sqlQuery)
        {
            SqlCommand mycommand = new SqlCommand();
            DataTable myDatatable = new DataTable();
            try
            {
                mycommand.CommandType = CommandType.Text;
                mycommand.Connection = openConnection();
                mycommand.CommandText = sqlQuery;
                SqlDataReader dt = mycommand.ExecuteReader();
                myDatatable.Load(dt);

            }
            catch (SqlException)
            {

                throw;
            }
            finally
            {
                if (cnn != null)
                {
                    cnn.Close();
                }
            }
            return myDatatable;
        }

        public bool insertQuery_NonQuery(string sqlQuery, SqlParameter[] myparamater)
        {
            SqlCommand mycommand = new SqlCommand();
            try
            {
              
                mycommand.Connection = openConnection();
                mycommand.CommandText = sqlQuery;
                mycommand.Parameters.AddRange(myparamater);
                mycommand.ExecuteNonQuery();
            }
            catch (SqlException)
            {

                return false;
            }
            finally
            {
                cnn.Close();
            }

            return true;
        }

        public int countQuery_ExecuteScalar(string sqlQuery)
        {
            SqlCommand mycommand = new SqlCommand();
            mycommand.Connection = openConnection();
            mycommand.CommandText = sqlQuery;
            return (int)mycommand.ExecuteScalar();
        }

    }
}